package db.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.db.Table;

import db.beans.M_CSVImportInterfaceBean;

public class M_CSVImportInterfaceTable extends Table{

	public static final String TABLE_NAME = "M_CSV_IMPORT_INTERFACE";

	public static final String COL_CSV_IMPORT_TYPE_ID = "CSV_IMPORT_TYPE_ID";
	public static final String COL_CSV_COL_ID = "CSV_COL_ID";
	public static final String COL_CSV_COL_NAME = "CSV_COL_NAME";
	public static final String COL_CSV_DATA_TYPE = "CSV_DATA_TYPE";
	public static final String COL_CSV_DATA_LENGTH = "CSV_DATA_LENGTH";
	public static final String COL_CSV_DATA_REQUIRED = "CSV_DATA_REQUIRED";
	public static final String COL_TARGET_DATA_TYPE = "TARGET_DATA_TYPE";
	public static final String COL_TARGET_TABLE_NAME = "TARGET_TABLE_NAME";
	public static final String COL_TARGET_COL_NAME = "TARGET_COL_NAME";
	public static final String COL_CHECK_MATCH_KEY= "CHECK_MATCH_KEY";


	public static M_CSVImportInterfaceBean[] selectByImportTypeName(Connection conn, String importTypeName) throws SQLException{
		final String sql = "SELECT "
			+ COL_CSV_IMPORT_TYPE_ID + ","
			+ COL_CSV_COL_ID + ","
			+ COL_CSV_COL_NAME + ","
			+ COL_CSV_DATA_TYPE + ","
			+ COL_CSV_DATA_LENGTH + ","
			+ COL_CSV_DATA_REQUIRED + ","
			+ COL_TARGET_DATA_TYPE + ","
			+ COL_TARGET_TABLE_NAME +","
			+ COL_TARGET_COL_NAME + ","
			+ COL_CHECK_MATCH_KEY
			+ " FROM "
			+ TABLE_NAME + ","
			+ M_CSVImportTypeTable.TABLE_NAME
			+ " WHERE "
			+ COL_CSV_IMPORT_TYPE_ID + "="
			+ "(SELECT " +  COL_CSV_IMPORT_TYPE_ID + " FROM " + M_CSVImportTypeTable.TABLE_NAME + " WHERE "
			+ M_CSVImportTypeTable.COL_IMPORT_TYPE_NAME + "=?)"
			;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Vector<M_CSVImportInterfaceBean> v = new Vector<M_CSVImportInterfaceBean>();
        try{
    		stmt = conn.prepareStatement(sql);
    		int i = 0;
    		stmt.setString(++i, importTypeName);
    		rs = stmt.executeQuery();
            while( rs.next() ) {
            	i = 0;
            	M_CSVImportInterfaceBean rec = new M_CSVImportInterfaceBean();
            	rec.setCsvImportTypeId(rs.getInt(++i));
            	rec.setCsvColId(rs.getInt(++i));
            	rec.setCsvColName(rs.getString(++i));
            	rec.setCsvDataType(rs.getString(++i));
            	rec.setCsvDataLength(rs.getInt(++i));
            	rec.setCsvDataRequired(rs.getInt(++i) == 1);
            	rec.setTargetDataType(rs.getString(++i));
            	rec.setTargetTableName(rs.getString(++i));
            	rec.setTargetColName(rs.getString(++i));
            	rec.setCheckMatchKey(rs.getString(++i));
            	v.add(rec);
            }

        } finally {
            if(rs != null){
                rs.close();
            }

            if (stmt != null){
            	stmt.close();
            }
        }
        return (M_CSVImportInterfaceBean[])v.toArray(new M_CSVImportInterfaceBean[v.size()]);
	}
}
